<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
               
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
    include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
    define('JPATH_BASE', dirname(__FILE__));
    require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();

require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

// Init excel
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("Wmc")
                             ->setLastModifiedBy("Wmc")
                             ->setTitle("Summary Statistic")
                             ->setSubject("Summary Statistic")
                             ->setDescription("Summary Statistic")
                             ->setKeywords("Summary Statistic")
                             ->setCategory("Summary Statistic");


// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:B1');
$activeSheet->setCellValue('A1', 'Summary Statistic Report');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);

$activeSheet->getColumnDimension('A')->setWidth(30);    
$activeSheet->getColumnDimension('B')->setWidth(30);   
        
// End init excel

/******** Query Data ********/  
$db = JFactory::getDbo();
$conditions = array();
$conditions['statistic_by'] = strval($_GET['st']);

$conditions['date_from'] = strval($_GET['f']);
$conditions['date_to'] = strval($_GET['t']);

$conditions['sex'] = strval($_GET['s']);
$conditions['age'] = strval($_GET['a']);
$conditions['job'] = strval($_GET['j']);
$conditions['location'] = strval($_GET['l']);

$query = reportProvider::memberStatistic($conditions);
if($conditions['statistic_by'] != ''){
    $db->setQuery($query);
    $data = $db->loadObjectList();   
    $index = 4;
    
    if($conditions['statistic_by'] == 'gender')
    {
        renderDataCell($activeSheet, $index, $data, 'Summary Statistic - by Sex');
    } 
    else if($conditions['statistic_by'] == 'location')
    {
        renderDataCell($activeSheet, $index, $data, 'Summary Statistic - by Location', $conditions['statistic_by']);
    } 
    else if($conditions['statistic_by'] == 'age')
    {
        renderDataCell($activeSheet, $index, $data, 'Summary Statistic - by Age Group', $conditions['statistic_by']);
    }
    else if($conditions['statistic_by'] == 'job')
    {
        renderDataCell($activeSheet, $index, $data, 'Summary Statistic - by Job Title/Position', $conditions['statistic_by']);
    }
    
}
else
{
    $data = array();
    $db->setQuery($query['gender']);
    $data[] = $db->loadObjectList();
    
    $db->setQuery($query['age']);
    $data[] = $db->loadObjectList();
    
    $db->setQuery($query['job']);
    $data[] = $db->loadObjectList();
    
    $db->setQuery($query['location']);
    $data[] = $db->loadObjectList();
    
    $index = 4;
    renderDataCell($activeSheet, $index, $data[0], 'Summary Statistic - by Sex', 'gender');
    $index = 8;
    renderDataCell($activeSheet, $index, $data[1], 'Summary Statistic - by Age Group', 'age');
        
    $index = 15;
    renderDataCell($activeSheet, $index, $data[2], 'Summary Statistic - by Job Title/Position', 'job');
    $index = 21;
    renderDataCell($activeSheet, $index, $data[3], 'Summary Statistic - by Location', 'location');
}


$fileName = 'summary_statistic_'.date('m-d-Y');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          
function renderDataCell($activeSheet, $index, $data, $text, $type='gender')
{
    setTopHeaderStyle($activeSheet, $text, 'A', $index++);
    if(!empty($data))
    {
        if($type == 'gender')
        {
            foreach($data as $key => $item):                           
            
                setHeaderStyle($activeSheet, $item->gender, 'A', $index, true);
                setHeaderStyle($activeSheet, number_format($item->count, 0, '.', '.'), 'B', $index++);
                
            endforeach;                
        }
        
        else if($type == 'location')
        {
            /************** FILL DATA **************/
            $districtsName = array('D1', 'D2', 'D3', 'D4', 'D5', 'D6', 'D7', 'D8', 'D9', 'D10', 'D11', 'D12', 'Binh Chanh', 'Binh Tan', 'Binh Thanh', 'Go Vap', 'Tan Binh', 'Tan Phu', 'Phu Nhuan', 'Thu Duc', 'Out of HCMC');
            $districts = array();
            foreach($districtsName as $item):
                $districts[] = array('key'=>$item, 'value'=>0);
            endforeach;
            $mapping = array(
                '5'=>'0',   '6'=>'1',   '7'=>'2',   '8'=>'3',   '9'=>'4',   '10'=>'5',   '11'=>'6',   '12'=>'7',   '13'=>'8',   '14'=>'9',
                '15'=>'10',   '16'=>'11',   '26'=>'12',   '23'=>'13',   '20'=>'14',   '17'=>'15',   '18'=>'16',   '19'=>'17',   '21'=>'18',   
                '22'=>'19'      
            );
            foreach($data as $item):         
                
                if($mapping[$item->distric] != ''):
                    $districts[$mapping[$item->distric]]['value'] += $item->count; 
                    $districts[$mapping[$item->distric]]['key'] = $item->name;
                else:
                    $districts[20]['value'] += $item->count; 
                    $districts[20]['key'] = 'Out of HCMC';
                endif; 
                
            endforeach;
                
            foreach($districts as $key => $item):
                
                setHeaderStyle($activeSheet, $item['key'], 'A', $index, true);
                setHeaderStyle($activeSheet, number_format($item['value'], 0, '.', '.'), 'B', $index++);
                
            endforeach;
        }
        
        else if($type == 'age')
        {
            $age = array(
                array('key'=>'Age 18-25', 'value'=>0),
                array('key'=>'Age 26-35', 'value'=>0),
                array('key'=>'Age 36-45', 'value'=>0),
                array('key'=>'Age 46+', 'value'=>0),
                array('key'=>'Unknown', 'value'=>0)
            );
            $currentYear = date('Y');
            
            foreach($data as $key => $item):  
                if(($currentYear-18) >= $item->dob && $item->dob >= ($currentYear-25) ):
                    $age[0]['value'] += $item->count;                    
                    
                elseif(($currentYear-26) >= $item->dob && $item->dob >= ($currentYear-35) ):
                    $age[1]['value'] += $item->count;
                    
                elseif(($currentYear-36) >= $item->dob && $item->dob >= ($currentYear-45) ):
                    $age[2]['value'] += $item->count;
                
                elseif(($currentYear-46) >= $item->dob && $item->dob >= ($currentYear-100) ):   
                    $age[3]['value'] += $item->count;
                    
                else:
                    $age[4]['value'] += $item->count;
                    
                endif;                                                         
            endforeach;
            
            foreach($age as $key => $item):
                
                setHeaderStyle($activeSheet, ucfirst($item['key']), 'A', $index, true);
                setHeaderStyle($activeSheet, number_format($item['value'], 0, '.', '.'), 'B', $index++);
            
            endforeach;
        }
        
        else if($type == 'job')
        {            
            $job = array();
            $job[] = array('key'=>'CEO/Chairman', 'value'=>0);
            $job[] = array('key'=>'Director', 'value'=>0);
            $job[] = array('key'=>'Mid Management & Executive Level', 'value'=>0);
            $job[] = array('key'=>'Other', 'value'=>0);
            foreach($data as $item):         
                              
                if($item->job == 1 || $item->job == 2):
                    $job[0]['value'] += $item->count; 
                    
                elseif($item->job == 3):
                    $job[1]['value'] += $item->count; 
                    
                elseif($item->job == 4 || $item->job == 5 || $item->job == 7):
                    $job[2]['value'] += $item->count; 
                    
                else:
                    $job[3]['value'] += $item->count; 
                    
                endif;
                
            endforeach;
            
            /******** Bind Data ********/
            foreach($job as $key => $item):
                                
                setHeaderStyle($activeSheet, $item['key'], 'A', $index, true);
                setHeaderStyle($activeSheet, number_format($item['value'], 0, '.', '.'), 'B', $index++);
                
            endforeach;
        }
        
        
    }
}

function setTopHeaderStyle($activeSheet, $value, $colName, $index, $bold=true)
{
    $activeSheet->mergeCells($colName.$index.':B'.$index);
    $activeSheet->setCellValue($colName.$index, $value);
    $style = $activeSheet->getStyle($colName.$index);
    $style->getFont()->setSize(14);
    $style->getFont()->setBold($bold);
    $style->getFont()->setColor( new PHPExcel_Style_Color( 'CC0000' ) );
}

function setHeaderStyle($activeSheet, $value, $colName, $colIndex, $bold=false)
{
    $activeSheet->setCellValueExplicit($colName.$colIndex, ucfirst($value), PHPExcel_Cell_DataType::TYPE_STRING);     
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    //$style->getFont()->setBold($bold);
    if($colName == 'B')
    {
        $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    }
}

function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{
    $fill = $activeSheet->getStyle($colName.$colIndex)->getFill();
    $fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $fill->getStartColor()->setARGB('cc99ff');  
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    //$style->getFont()->setBold(true);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
